﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LBSoft.Common.DotNetCode;
using LBSoft.Common.DotNetExt;
using LBSoft.DataBase;

namespace LBSoft.Business.NewDAL
{
    public class CommonDal : BaseDAL
    {
        protected IDbNewHelper helper = DataFactory.SqlNewDataBase();
        public CommonDal()
        {

        }

        public CommonDal(string connectstring)
        {
            helper = DataFactory.SqlNewDataBase(connectstring);
        }

        public Hashtable GetDataByID(string tableName, string pkName, string pkVal)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Select * From ").Append(tableName).Append(" Where ").Append(pkName).Append("='" + pkVal + "'");
            DataTable dt = this.GetDataTableBySQL(sb);
            return DataTableToHashtable(dt);
        }
        private Hashtable DataTableToHashtable(DataTable dt)
        {
            Hashtable ht = new Hashtable();
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string key = dt.Columns[i].ColumnName;
                    ht[key.ToUpper()] = dr[key];
                }
            }
            return ht;
        }

        public int ExecuteBySql(StringBuilder sql)
        {
            return this.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }

        public object ExecuteScalar(string sql)
        {
            return helper.ExecuteScalar(CommandType.Text, sql, null);
        }

        /// <summary>
        /// 获取数据（返回字段保持原格式）
        /// </summary>
        /// <param name="sql">StringBuilder</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTableBySQL(StringBuilder sql)
        {
            DataSet ds = this.ExecuteDataSet(CommandType.Text, sql.ToString(), null);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return null;
        }
        /// <summary>
        /// 获取数据（返回字段大写）
        /// </summary>
        /// <param name="sql">StringBuilder</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTableBySQL2(StringBuilder sql)
        {
            DataSet ds = this.ExecuteDataSet(CommandType.Text, sql.ToString(), null);
            if (ds != null && ds.Tables.Count > 0)
            {
                return GetNewDataTable(ds.Tables[0]);
            }
            return null;
        }
        /// <summary>
        /// 返回字段大写
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private DataTable GetNewDataTable(DataTable dt)
        {
            DataTable newDt = new DataTable();
            if (dt != null)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    newDt.Columns.Add(dc.ColumnName.ToUpper(), dc.DataType);
                }
                foreach (DataRow dr in dt.Rows)
                {
                    newDt.LoadDataRow(dr.ItemArray, true);
                }
                return newDt;
            }
            return null;
        }

        public bool SubmitData(string tableName, string pkName, string pkVal, Hashtable ht)
        {
            //移除UID--
            if (ht != null && ht.ContainsKey("UID"))
            {
                ht.Remove("UID");
            }
            bool result;
            if (string.IsNullOrEmpty(pkVal))
            {
                result = (this.InsertByHashtable(tableName, ht) > 0);
            }
            else
            {
                result = (this.UpdateByHashtable(tableName, pkName, pkVal, ht) > 0);
            }
            return result;
        }

        private int InsertByHashtable(string tableName, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Insert Into ");
            sb.Append(tableName);
            sb.Append("(");
            StringBuilder sp = new StringBuilder();
            StringBuilder sb_prame = new StringBuilder();
            foreach (string key in ht.Keys)
            {
                sb_prame.Append("," + key);
                sp.Append(",@" + key);
            }
            sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
            sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
            return this.ExecuteNonQuery(CommandType.Text, sb.ToString(), this.GetParameter(ht));
        }

        private int UpdateByHashtable(string tableName, string pkName, string pkVal, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Update ");
            sb.Append(tableName);
            sb.Append(" Set ");
            bool isFirstValue = true;
            foreach (string key in ht.Keys)
            {
                if (isFirstValue)
                {
                    isFirstValue = false;
                    sb.Append(key);
                    sb.Append("=");
                    sb.Append("@" + key);
                }
                else
                {
                    sb.Append("," + key);
                    sb.Append("=");
                    sb.Append("@" + key);
                }
            }
            sb.Append(" Where ").Append(pkName).Append("=").Append("@" + pkName);
            ht[pkName] = pkVal;
            SqlParameter[] _params = this.GetParameter(ht);
            return this.ExecuteNonQuery(CommandType.Text, sb.ToString(), _params);
        }

        private SqlParameter[] GetParameter(Hashtable ht)
        {
            SqlParameter[] _params = new SqlParameter[ht.Count];
            int i = 0;
            foreach (string key in ht.Keys)
            {
                _params[i] = new SqlParameter("@" + key, ht[key]);
                i++;
            }
            return _params;
        }


        /// <summary>
        /// 分页获取数据，自定义排序
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="cols"></param>
        /// <param name="SqlWhere"></param>
        /// <param name="order"></param>
        /// <param name="IList_param"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable GetListByPage(string tabName, string cols, StringBuilder SqlWhere, string order, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT " + cols + " from " + tabName);
            strSql.Append(SqlWhere);
            return this.GetNewDataTable(helper.GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), order, pageIndex, pageSize, ref count));
        }
       

        /// <summary>
        /// 分页获取数据，默认按创建时间排序
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="SqlWhere"></param>
        /// <param name="IList_param"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable GetListByPage(string tabName, StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * from " + tabName);
            strSql.Append(SqlWhere);
            return this.GetNewDataTable(helper.GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "CreateTime", "Desc", pageIndex, pageSize, ref count));
        }
    }
}
